package com.hzlh.excel;

import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.beanutils.PropertyUtils;
//import org.springframework.web.servlet.view.document.AbstractJExcelView;
import org.codehaus.jackson.map.JsonMappingException;
import org.codehaus.jackson.map.ObjectMapper;

import com.google.gson.JsonParseException;
import com.hzlh.common.util.DateUtil;

/**
 * @ClassName: JXLExcelView <公用导出excel>
 * @Description: excel视图导出，基于效率、性能考虑采用Jxl，大数据处理比poi快
 * @author liu_wenfeng
 * @date 2014年9月5日 上午9:32:29
 */
public class JXLExcelView extends AbstractJxlExcelView {

	//参数格式，测试
	/**
	例如：
	private String[] columnNames = new String[] { "编号", "姓名", "年龄", "性别", "密码", "地址" };
	private String[] dbColumnNames = new String[] { "id", "name", "age", "sex", "password", "address" };
	private String[] columnWidths = new String[] { 20, 20, 20, 20, 20, 20 };
	*/
	public static final String SPECIAL = "special";//特殊标示
	private String[] columnNames = new String[]{};
	private String[] dbColumnNames = new String[]{};
	private String[] columnWidths = new String[]{};
	private Map<String, Map<String, Object>> jsonMap = null;

	@Override
	public void buildExcelDocument(Map<String, Object> map, WritableWorkbook work, HttpServletRequest req,
			HttpServletResponse response) {
		String [] titles = (String[])map.get("titles");
		if(null != titles && titles.length > 0){
			 columnNames = titles;
		}
		String [] columns = (String[])map.get("columns");
		if(null != columns &&  columns.length > 0){
			dbColumnNames = columns;
		}
		String[] widths = (String[])map.get("widths");
		if(null != widths &&  widths.length > 0){
			columnWidths = widths;
		}
		String[] specials = (String[])map.get(SPECIAL);
		if(null != specials && specials.length > 0){
			jsonMap = readJsonMap(specials[0]);
		}
		
		OutputStream os = null;
		try {

			String excelName = "data-"+DateUtil.dateStr(new Date(), "yyyy-MM-dd-HH-mm-ss")+".xls";
			// 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
			response.setContentType("APPLICATION/OCTET-STREAM");
			response.setHeader("Content-Disposition", "attachment; filename="
					+ URLEncoder.encode(excelName, "UTF-8"));
			os = response.getOutputStream();
			// sheet名称
			String sheetName = (String)map.get("sname");

			// 全局设置
			WorkbookSettings setting = new WorkbookSettings();
			java.util.Locale locale = new java.util.Locale("zh", "CN");
			setting.setLocale(locale);
			setting.setEncoding("ISO-8859-1");
			// 创建工作薄
			work = Workbook.createWorkbook(os); // 建立excel文件
			// 创建第一个工作表
			jxl.write.WritableSheet ws = work.createSheet(sheetName, 1); // sheet名称
			// 添加标题
			addColumNameToWsheet(ws);
			
			writeContext(ws, (List<?>)map.get("list"));

		} catch (Exception e) {
			e.printStackTrace();
		} finally {

			// 写入文件
			try {
				work.write();
				work.close();
				os.flush();
				os.close();
			} catch (WriteException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}

		}

	}

	/**
	 * @Description: 读写excel内容
	 * @param wsheet
	 * @param list void  
	 * @throws
	 * @author liu_wenfeng
	 * @date 2014年9月5日 上午9:55:45
	 */
	private <T> void writeContext(WritableSheet wsheet, List<T> list) {
		int rows = list.size();
		jxl.write.Label wlabel = null;
		jxl.write.WritableCellFormat wcf = getFormat();
		int cols = dbColumnNames.length;
		String columnName = null;
		Object value = null;
		try {
			for (int i = 0; i < rows; i++) {
				T t = (T) list.get(i);				
				for (int j = 0; j < cols; j++) {
					columnName = dbColumnNames[j];//dbColumnNames[j].toLowerCase()转换小写  - liu_wenfeng
//					value = PropertyUtils.getProperty(t, columnName);
					//如果为时间类型转换yyyy-MM-dd HH:mm:ss - liu_wenfeng
					if(PropertyUtils.getProperty(t, columnName) instanceof Date){
						value = DateUtil.dateStr((Date)PropertyUtils.getProperty(t, columnName));
					}else{
						value = PropertyUtils.getProperty(t, columnName);
						//特殊字段 例如：key="special" value={"isSale": {"0": "否","1": "是"},"isSale1": {"0": "否","1": "是"},....}
						if(jsonMap.containsKey(columnName) && jsonMap.size() > 0){
							//处理特殊字段
							Set<String> key = jsonMap.keySet();
							Iterator<String> iter = key.iterator();
							while (iter.hasNext()) {
								String field = iter.next();
								//如果列名等于特殊字符key名称与map对象中存在key，则下一步
								Map<String, Object> valMap = jsonMap.get(field);
								if(valMap.size() > 0){
									if(field.equals(columnName) && valMap.containsKey(value.toString())){
										value = valMap.get(value.toString());
									}
								}
							}
						}
					}
					wlabel = new jxl.write.Label(j, (i + 1), value + "", wcf);
					wlabel = new jxl.write.Label(j, (i + 1), value + "");
					wsheet.addCell(wlabel);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * @Description: 添加标题样式
	 * @param wsheet
	 * @throws RowsExceededException
	 * @throws WriteException void  
	 * @throws
	 * @author liu_wenfeng
	 * @date 2014年9月5日 上午9:56:28
	 */
	private void addColumNameToWsheet(jxl.write.WritableSheet wsheet)
			throws RowsExceededException, WriteException {

		// 设置excel标题
		jxl.write.WritableFont wfont = getFont();
		if (null == wfont) {
			wfont = new WritableFont(WritableFont.ARIAL,
					WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD);

		}
		jxl.write.WritableCellFormat wcfFC = getFormat();
		if (null == wcfFC) {
			wcfFC = new jxl.write.WritableCellFormat(wfont);
			try {
				wcfFC.setWrap(true);// 自动换行
				wcfFC.setAlignment(Alignment.CENTRE);
				wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);// 设置对齐方式
			} catch (WriteException e) {
				e.printStackTrace();
			}
		}

		jxl.write.Label wlabel1 = null;
		String[] columNames = columnNames;
		if (null == columNames)
			return;
		int colSize = columNames.length;

		String[] colsWidth = columnWidths;
		if (null == colsWidth) {
			colsWidth = new String[colSize];
			for (int i = 0; i < colSize; i++) {
				colsWidth[i] = "20";
			}
		}

		int temp = 0;
		String colName = null;
		for (int i = 0; i < colSize; i++) {
			colName = columNames[i];
			if (null == colName || "".equals(colName))
				colName = "";
			wlabel1 = new jxl.write.Label(i, 0, colName, wcfFC);
			wsheet.addCell(wlabel1);
			temp = Integer.parseInt(colsWidth[i]);
			// 默认设置列宽
			temp = temp == 0 ? 20 : temp;
			wsheet.setColumnView(i, temp);
		}

	}

	/**
	 * @Description: 设置格式
	 * @return WritableCellFormat  
	 * @throws
	 * @author liu_wenfeng
	 * @date 2014年9月5日 上午9:56:43
	 */
	private WritableCellFormat getFormat() {

		jxl.write.WritableFont wfont = getFont();
		jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(
				wfont);
		try {
			wcfFC.setWrap(true);
			wcfFC.setAlignment(Alignment.CENTRE);
			wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);
		} catch (WriteException e) {
			e.printStackTrace();
		}
		return wcfFC;
	}

	/**
	 * @Description: 设置字体
	 * @return WritableFont  
	 * @throws
	 * @author liu_wenfeng
	 * @date 2014年9月5日 上午9:56:53
	 */
	private WritableFont getFont() {
		return new WritableFont(WritableFont.ARIAL,
				WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD);
	}
	
	/**
	 * @Description: 读取Json转换Map对象
	 * @param jsonText
	 * @return Map<String,Map<String,Object>>  
	 * @throws
	 * @author liu_wenfeng
	 * @date 2014年9月9日 下午5:44:48
	 */
	@SuppressWarnings("unchecked")
	private Map<String, Map<String, Object>> readJsonMap(String jsonText){
		Map<String, Map<String, Object>> result = null;
		try {
			result = new ObjectMapper().readValue(jsonText, Map.class);
		} catch (JsonParseException e) {
			e.printStackTrace();
		} catch (JsonMappingException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return result;
	}

}
